const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class Database {
  constructor(dataDir = null) {
    // 如果没有指定数据目录，使用模块目录下的data文件夹
    const defaultDataDir = path.join(__dirname, '..', 'data');
    this.dataDir = dataDir || defaultDataDir;
    this.dbPath = path.join(this.dataDir, 'douyin_publisher.db');
    this.db = null;
    
    // 确保数据目录存在
    const fs = require('fs');
    if (!fs.existsSync(this.dataDir)) {
      fs.mkdirSync(this.dataDir, { recursive: true });
    }
  }

  init() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbPath, (err) => {
        if (err) {
          console.error('❌ 数据库连接失败:', err);
          reject(err);
          return;
        }
        
        console.log('✅ 数据库连接成功');
        this.createTables().then(resolve).catch(reject);
      });
    });
  }

  createTables() {
    return new Promise((resolve, reject) => {
      const createAccountsTable = `
        CREATE TABLE IF NOT EXISTS accounts (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          account_id TEXT UNIQUE NOT NULL,
          name TEXT NOT NULL,
          platform TEXT DEFAULT 'douyin',
          cookies TEXT,
          cookies_valid INTEGER DEFAULT 0,
          last_validated_at DATETIME,
          created_at DATETIME DEFAULT (datetime('now', 'localtime')),
          updated_at DATETIME DEFAULT (datetime('now', 'localtime'))
        )
      `;

      const createPublishRecordsTable = `
        CREATE TABLE IF NOT EXISTS publish_records (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          account_id TEXT NOT NULL,
          title TEXT NOT NULL,
          content TEXT,
          content_type TEXT NOT NULL,
          file_path TEXT,
          file_url TEXT,
          tags TEXT,
          status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'processing', 'published', 'failed')),
          publish_time DATETIME,
          error_message TEXT,
          error_code TEXT,
          created_at DATETIME DEFAULT (datetime('now', 'localtime')),
          FOREIGN KEY (account_id) REFERENCES accounts (account_id)
        )
      `;

      this.db.run(createAccountsTable, (err) => {
        if (err) {
          reject(err);
          return;
        }
        
        this.db.run(createPublishRecordsTable, (err) => {
          if (err) {
            reject(err);
            return;
          }
          
          resolve();
        });
      });
    });
  }

  saveAccount(accountData, allowUpdate = false) {
    return new Promise((resolve, reject) => {
      const { account_id, name, platform, cookies } = accountData;
      
      // 检查必要字段
      if (!account_id) {
        reject(new Error('account_id 字段是必需的'));
        return;
      }
      
      // 首先检查账号是否已存在
      const checkSql = 'SELECT id FROM accounts WHERE account_id = ?';
      this.db.get(checkSql, [account_id], (checkErr, existingRow) => {
        if (checkErr) {
          reject(checkErr);
          return;
        }
        
        if (existingRow && !allowUpdate) {
          // 账号已存在且不允许更新，返回错误
          reject(new Error(`账号ID "${account_id}" 已存在，请使用不同的账号ID`));
          return;
        }
        
        // 账号不存在，创建新账号；或者允许更新，使用 INSERT OR REPLACE
        const cookiesStr = JSON.stringify(cookies);
        const sql = allowUpdate ? 
          `INSERT OR REPLACE INTO accounts (account_id, name, platform, cookies, updated_at) 
           VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)` :
          `INSERT INTO accounts (account_id, name, platform, cookies, updated_at) 
           VALUES (?, ?, ?, ?, CURRENT_TIMESTAMP)`;
        
        this.db.run(sql, [account_id, name, platform, cookiesStr], function(err) {
          if (err) {
            reject(err);
            return;
          }
          
          // 获取保存后的完整账号信息
          const selectSql = 'SELECT * FROM accounts WHERE account_id = ?';
          this.db.get(selectSql, [account_id], (selectErr, row) => {
            if (selectErr) {
              reject(selectErr);
              return;
            }
            
            const account = {
              ...row,
              cookies: row.cookies ? JSON.parse(row.cookies) : []
            };
            
            resolve({ id: account.id, account_id, name, platform, cookies });
          });
        }.bind(this));
      });
    });
  }

  getAccounts() {
    return new Promise((resolve, reject) => {
      const sql = 'SELECT * FROM accounts ORDER BY created_at DESC';
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        
        // 解析 cookies
        const accounts = rows.map(row => ({
          ...row,
          cookies: row.cookies ? JSON.parse(row.cookies) : []
        }));
        
        resolve(accounts);
      });
    });
  }

  getAccountById(idOrAccountId) {
    return new Promise((resolve, reject) => {
      // 首先尝试按 account_id 查询，如果没找到再按数字 id 查询
      const sql = 'SELECT * FROM accounts WHERE account_id = ? OR (id = ? AND ? REGEXP "^[0-9]+$" AND LENGTH(?) <= 10)';
      
      // 简化逻辑：直接尝试两种查询方式
      const accountIdSql = 'SELECT * FROM accounts WHERE account_id = ?';
      const numericIdSql = 'SELECT * FROM accounts WHERE id = ?';
      
      // 先尝试按 account_id 查询
      this.db.get(accountIdSql, [idOrAccountId], (err, row) => {
        if (err) {
          reject(err);
          return;
        }
        
        if (row) {
          const account = {
            ...row,
            cookies: row.cookies ? JSON.parse(row.cookies) : []
          };
          resolve(account);
          return;
        }
        
        // 如果按 account_id 没找到，且参数是纯数字且长度较短，尝试按数字 id 查询
        const isShortNumeric = /^\d{1,10}$/.test(idOrAccountId);
        if (!isShortNumeric) {
          resolve(null);
          return;
        }
        
        this.db.get(numericIdSql, [parseInt(idOrAccountId)], (err2, row2) => {
          if (err2) {
            reject(err2);
            return;
          }
          
          if (!row2) {
            resolve(null);
            return;
          }
          
          const account = {
            ...row2,
            cookies: row2.cookies ? JSON.parse(row2.cookies) : []
          };
          resolve(account);
        });
      });
    });
  }

  deleteAccount(accountId) {
    return new Promise((resolve, reject) => {
      const sql = 'DELETE FROM accounts WHERE id = ?';
      this.db.run(sql, [accountId], (err) => {
        if (err) {
          reject(err);
          return;
        }
        resolve();
      });
    });
  }

  savePublishRecord(recordData) {
    return new Promise((resolve, reject) => {
      const { account_id, title, content, content_type, file_path, file_url, tags, status, publish_time, error_message, error_code } = recordData;
      // 创建新记录
      const sql = `
        INSERT INTO publish_records (account_id, title, content, content_type, file_path, file_url, tags, status, publish_time, error_message, error_code) 
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `;
      this.db.run(sql, [account_id, title, content, content_type, file_path, file_url, tags, status, publish_time, error_message, error_code], function(err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: this.lastID, ...recordData });
      });
    });
  }

  updatePublishRecordStatus(recordId, statusData) {
    return new Promise((resolve, reject) => {
      const { status, publish_time, error_message, error_code } = statusData;
      const sql = `
        UPDATE publish_records 
        SET status = ?, publish_time = ?, error_message = ?, error_code = ?
        WHERE id = ?
      `;
      this.db.run(sql, [status, publish_time, error_message, error_code, recordId], function(err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ id: recordId, ...statusData });
      });
    });
  }

  getPublishRecords() {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name, a.account_id as account_custom_id
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        ORDER BY pr.created_at DESC
      `;
      this.db.all(sql, [], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      });
    });
  }

  // 🆕 通过 account_id 获取发布记录
  getPublishRecordsByAccountId(accountId) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name, a.account_id as account_custom_id
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        WHERE pr.account_id = ?
        ORDER BY pr.created_at DESC
      `;
      this.db.all(sql, [accountId], (err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      });
    });
  }

  // 🆕 通过 id 获取单个发布记录
  getPublishRecordById(recordId) {
    return new Promise((resolve, reject) => {
      const sql = `
        SELECT pr.*, a.name as account_name, a.account_id as account_custom_id
        FROM publish_records pr 
        LEFT JOIN accounts a ON pr.account_id = a.account_id 
        WHERE pr.id = ?
      `;
      this.db.get(sql, [recordId], (err, row) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(row);
      });
    });
  }

  // 🆕 通过 account_id 获取账号信息（用于cookie验证）
  getAccountByAccountId(accountId) {
    return new Promise((resolve, reject) => {
      const sql = 'SELECT * FROM accounts WHERE account_id = ?';
      this.db.get(sql, [accountId], (err, row) => {
        if (err) {
          reject(err);
          return;
        }
        
        if (!row) {
          resolve(null);
          return;
        }
        
        const account = {
          ...row,
          cookies: row.cookies ? JSON.parse(row.cookies) : []
        };
        resolve(account);
      });
    });
  }

  // 🆕 更新账号的验证状态
  updateAccountValidationStatus(accountId, isValid) {
    return new Promise((resolve, reject) => {
      const sql = `
        UPDATE accounts 
        SET cookies_valid = ?, last_validated_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
        WHERE account_id = ?
      `;
      this.db.run(sql, [isValid ? 1 : 0, accountId], function(err) {
        if (err) {
          reject(err);
          return;
        }
        resolve({ changes: this.changes });
      });
    });
  }

  close() {
    if (this.db) {
      this.db.close();
    }
  }
}

module.exports = Database; 